﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class PickDetailDA : IPickDetailDA
    {
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string BATCH_CARTON_ID = "@BATCH_CARTON_ID";
        private string CARTON_GROUP = "@CARTON_GROUP";
        private string CARTON_TYPE = "@CARTON_TYPE";
        private string CASE_ID = "@CASE_ID";
        private string COMPANY_PREFIX = "@COMPANY_PREFIX";
        private string CREATED_BY = "@CREATED_BY";
        private string CROSS_DOCKED = "@CROSS_DOCKED";
        private string DO_CARTONIZE = "@DO_CARTONIZE";
        private string DO_REPLENISH = "@DO_REPLENISH";
        private string DOOR = "@DOOR";
        private string DROP_ID = "@DROP_ID";
        private string EFFECTIVE_DATE = "@EFFECTIVE_DATE";
        private string FREIGHT_CHARGES = "@FREIGHT_CHARGES";
        private string FROM_LOC = "@FROM_LOC";
        private string INTER_MODAL_VEHICLE = "@INTER_MODAL_VEHICLE";
        private string IS_CLOSED = "@IS_CLOSED";
        private string LABEL_TYPE = "@LABEL_TYPE";
        private string LOAD_ID = "@LOAD_ID";
        private string LOC = "@LOC";
        private string LOT = "@LOT";
        private string LPN_ID = "@LPN_ID";
        private string MOVED_QTY = "@MOVED_QTY";
        private string OPTIMIZE_COP = "@OPTIMIZE_COP";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PD_UDF1 = "@PD_UDF1";
        private string PD_UDF2 = "@PD_UDF2";
        private string PD_UDF3 = "@PD_UDF3";
        private string PICK_DETAIL_ID = "@PICK_DETAIL_ID";
        private string PICK_METHOD = "@PICK_METHOD";
        private string PICK_NOTES = "@PICK_NOTES";
        private string PREALLOCATE_PICK_DETAIL_ID = "@PREALLOCATE_PICK_DETAIL_ID";
        private string QC_STATUS = "@QC_STATUS";
        private string QTY = "@QTY";
        private string QTY_REJECTED = "@QTY_REJECTED";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string REJECTED_REASON = "@REJECTED_REASON";
        private string REPLENISH_ZONE = "@REPLENISH_ZONE";
        private string ROUTE = "@ROUTE";
        private string SEQ_NO = "@SEQ_NO";
        private string SERIAL_REFERENCE = "@SERIAL_REFERENCE";
        private string SKU_ID = "@SKU_ID";
        private string SORTATION_LOCATION = "@SORTATION_LOCATION";
        private string SORTATION_STATION = "@SORTATION_STATION";
        private string SQL_CHECK_PICK_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@PICK_DETAIL WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID ";
        private string SQL_DELETE_PICK_DETAIL = " DELETE FROM @_@PICK_DETAIL WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID ";
        private string SQL_INSERT_PICK_DETAIL = " INSERT INTO @_@PICK_DETAIL ( WH_ID, PICK_DETAIL_ID, CASE_ID, ORDER_ID, ORDER_LINE_ID,PREALLOCATE_PICK_DETAIL_ID, LOT, OWNER_ID, SKU_ID, ALT_SKU_ID, UOM_ID, QTY, UOM_QTY, MOVED_QTY, STATUS, DROP_ID, LOC, LPN_ID, PACK_ID, UPDATE_SOURCE, CARTON_GROUP, CARTON_TYPE, TO_LOC, DO_REPLENISH, REPLENISH_ZONE, DO_CARTONIZE, PICK_METHOD, WAVE_ID, EFFECTIVE_DATE, FROM_LOC, TRACKING_ID, FREIGHT_CHARGES, INTER_MODAL_VEHICLE, LOAD_ID, STOP, DOOR, ROUTE, SORTATION_LOCATION, SORTATION_STATION, BATCH_CARTON_ID, IS_CLOSED, QC_STATUS, PD_UDF1, PD_UDF2, PD_UDF3, PICK_NOTES, RECEIPT_ID, CROSS_DOCKED, SEQ_NO, LABEL_TYPE, COMPANY_PREFIX, SERIAL_REFERENCE, OPTIMIZE_COP, QTY_REJECTED, REJECTED_REASON, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @PICK_DETAIL_ID, @CASE_ID, @ORDER_ID, @ORDER_LINE_ID,@PREALLOCATE_PICK_DETAIL_ID, @LOT, @OWNER_ID, @SKU_ID, @ALT_SKU_ID, @UOM_ID, @QTY, @UOM_QTY, @MOVED_QTY, @STATUS, @DROP_ID, @LOC, @LPN_ID, @PACK_ID, @UPDATE_SOURCE, @CARTON_GROUP, @CARTON_TYPE, @TO_LOC, @DO_REPLENISH, @REPLENISH_ZONE, @DO_CARTONIZE, @PICK_METHOD, @WAVE_ID, @EFFECTIVE_DATE, @FROM_LOC, @TRACKING_ID, @FREIGHT_CHARGES, @INTER_MODAL_VEHICLE, @LOAD_ID, @STOP, @DOOR, @ROUTE, @SORTATION_LOCATION, @SORTATION_STATION, @BATCH_CARTON_ID, @IS_CLOSED, @QC_STATUS, @PD_UDF1, @PD_UDF2, @PD_UDF3, @PICK_NOTES, @RECEIPT_ID, @CROSS_DOCKED, @SEQ_NO, @LABEL_TYPE, @COMPANY_PREFIX, @SERIAL_REFERENCE, @OPTIMIZE_COP, @QTY_REJECTED, @REJECTED_REASON, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_PICK_DETAIL = " SELECT  PD.ROWID, PD.WH_ID, PD.PICK_DETAIL_ID, PD.CASE_ID, PD.ORDER_ID, PD.ORDER_LINE_ID,PREALLOCATE_PICK_DETAIL_ID, PD.LOT, PD.OWNER_ID, PD.SKU_ID, PD.ALT_SKU_ID, PD.UOM_ID, PD.QTY, PD.UOM_QTY, PD.MOVED_QTY, T.[TEXT] AS STATUS_D,PD.STATUS, PD.DROP_ID, PD.LOC, PD.LPN_ID, PD.PACK_ID, PD.UPDATE_SOURCE, PD.CARTON_GROUP, PD.CARTON_TYPE, PD.TO_LOC, PD.DO_REPLENISH, PD.REPLENISH_ZONE, PD.DO_CARTONIZE, PD.PICK_METHOD, PD.WAVE_ID, PD.EFFECTIVE_DATE, PD.FROM_LOC, PD.TRACKING_ID, PD.FREIGHT_CHARGES, PD.INTER_MODAL_VEHICLE, PD.LOAD_ID, PD.STOP, PD.DOOR, PD.ROUTE, PD.SORTATION_LOCATION, PD.SORTATION_STATION, PD.BATCH_CARTON_ID, PD.IS_CLOSED, PD.QC_STATUS, PD.PD_UDF1, PD.PD_UDF2, PD.PD_UDF3, PD.PICK_NOTES, PD.RECEIPT_ID, PD.CROSS_DOCKED, PD.SEQ_NO, PD.LABEL_TYPE, PD.COMPANY_PREFIX, PD.SERIAL_REFERENCE, PD.OPTIMIZE_COP, PD.QTY_REJECTED, PD.REJECTED_REASON, PD.TS, PD.CREATED_BY, PD.CREATED_DATE, PD.UPDATED_BY, PD.UPDATED_DATE, OD.EXTERN_ORDER_ID, OD.REFERENCE_NUM,S.DESCR AS SKU_DESCR, LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07, LA.LOT_ATTR08, LA.LOT_ATTR09, LA.LOT_ATTR10, LA.PRODUCE_DATE, LA.EXPIRY_DATE, LA.RECEIPT_DATE, LA.EXTERNAL_LOT,LA.VENDOR_ID  FROM  @_@PICK_DETAIL PD LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PD.STATUS AND SCD.SYS_CODE_TYPE = 'PickDetailStatus' LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = PD.LOT LEFT JOIN sysadm.TRANSLATION T ON 'PickDetailStatus.'+PD.STATUS = T.ITEM_ID  \t AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  INNER JOIN @_@ORDERS AS OD ON PD.ORDER_ID = OD.ORDER_ID  LEFT JOIN  @_@SKU S  ON PD.SKU_ID= S.SKU_ID AND PD.OWNER_ID =S.OWNER_ID";
        private string SQL_SELECT_PICK_DETAIL_ID_BY_LPN = " SELECT PICK_DETAIL_ID FROM @_@PICK_DETAIL WHERE LPN_ID = @LPN_ID ";
        private string SQL_SELECT_PICK_DETAIL_ID_BY_ORDER_ID = " SELECT PICK_DETAIL_ID FROM @_@PICK_DETAIL WHERE ORDER_ID = @ORDER_ID AND STATUS = @STATUS ";
        private string SQL_SELECT_PICK_DETAIL_ID_BY_ORDER_LINE_ID = " SELECT PICK_DETAIL_ID FROM @_@PICK_DETAIL WHERE ORDER_ID = @ORDER_ID AND ORDER_LINE_ID = @ORDER_LINE_ID AND STATUS = @STATUS ";
        private string SQL_SELECT_PICK_DETAIL_WAVE_DETAIL = " SELECT  PD.ROWID, PD.WH_ID, PD.PICK_DETAIL_ID, PD.CASE_ID, PD.ORDER_ID, PD.ORDER_LINE_ID,PREALLOCATE_PICK_DETAIL_ID, PD.LOT, PD.OWNER_ID, PD.SKU_ID, PD.ALT_SKU_ID, PD.UOM_ID, PD.QTY, PD.UOM_QTY, PD.MOVED_QTY, T.[TEXT] AS STATUS_D,PD.STATUS, PD.DROP_ID, PD.LOC, PD.LPN_ID, PD.PACK_ID, PD.UPDATE_SOURCE, PD.CARTON_GROUP, PD.CARTON_TYPE, PD.TO_LOC, PD.DO_REPLENISH, PD.REPLENISH_ZONE, PD.DO_CARTONIZE, PD.PICK_METHOD, PD.WAVE_ID, PD.EFFECTIVE_DATE, PD.FROM_LOC, PD.TRACKING_ID, PD.FREIGHT_CHARGES, PD.INTER_MODAL_VEHICLE, PD.LOAD_ID, PD.STOP, PD.DOOR, PD.ROUTE, PD.SORTATION_LOCATION, PD.SORTATION_STATION, PD.BATCH_CARTON_ID, PD.IS_CLOSED, PD.QC_STATUS, PD.PD_UDF1, PD.PD_UDF2, PD.PD_UDF3, PD.PICK_NOTES, PD.RECEIPT_ID, PD.CROSS_DOCKED, PD.SEQ_NO, PD.LABEL_TYPE, PD.COMPANY_PREFIX, PD.SERIAL_REFERENCE, PD.OPTIMIZE_COP, PD.QTY_REJECTED, PD.REJECTED_REASON, PD.TS, PD.CREATED_BY, PD.CREATED_DATE, PD.UPDATED_BY, PD.UPDATED_DATE, OD.EXTERN_ORDER_ID, OD.REFERENCE_NUM,S.DESCR AS SKU_DESCR, LA.LOT_ATTR01, LA.LOT_ATTR02, LA.LOT_ATTR03, LA.LOT_ATTR04, LA.LOT_ATTR05, LA.LOT_ATTR06, LA.LOT_ATTR07, LA.LOT_ATTR08, LA.LOT_ATTR09, LA.LOT_ATTR10, LA.PRODUCE_DATE, LA.EXPIRY_DATE, LA.RECEIPT_DATE, LA.EXTERNAL_LOT,LA.VENDOR_ID,WD.WAVE_ID AS WD_WAVE_ID  FROM  @_@PICK_DETAIL PD LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PD.STATUS AND SCD.SYS_CODE_TYPE = 'PickDetailStatus' LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = PD.LOT LEFT JOIN sysadm.TRANSLATION T ON 'PickDetailStatus.'+PD.STATUS = T.ITEM_ID  \t AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON PD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  INNER JOIN @_@ORDERS AS OD ON PD.ORDER_ID = OD.ORDER_ID  LEFT JOIN  @_@WAVE_DETAIL WD  ON PD.ORDER_ID= WD.ORDER_ID LEFT JOIN  @_@SKU S  ON PD.SKU_ID= S.SKU_ID AND PD.OWNER_ID =S.OWNER_ID";
        private string SQL_UPDATE_PICK_DETAIL = " UPDATE @_@PICK_DETAIL SET WH_ID = @WH_ID, CASE_ID = @CASE_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, PREALLOCATE_PICK_DETAIL_ID = @PREALLOCATE_PICK_DETAIL_ID, LOT = @LOT, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, UOM_ID = @UOM_ID, QTY = @QTY, UOM_QTY = @UOM_QTY, MOVED_QTY = @MOVED_QTY, STATUS = @STATUS, DROP_ID = @DROP_ID, LOC = @LOC, LPN_ID = @LPN_ID, PACK_ID = @PACK_ID, UPDATE_SOURCE = @UPDATE_SOURCE, CARTON_GROUP = @CARTON_GROUP, CARTON_TYPE = @CARTON_TYPE, TO_LOC = @TO_LOC, DO_REPLENISH = @DO_REPLENISH, REPLENISH_ZONE = @REPLENISH_ZONE, DO_CARTONIZE = @DO_CARTONIZE, PICK_METHOD = @PICK_METHOD, WAVE_ID = @WAVE_ID, EFFECTIVE_DATE = @EFFECTIVE_DATE, FROM_LOC = @FROM_LOC, TRACKING_ID = @TRACKING_ID, FREIGHT_CHARGES = @FREIGHT_CHARGES, INTER_MODAL_VEHICLE = @INTER_MODAL_VEHICLE, LOAD_ID = @LOAD_ID, STOP = @STOP, DOOR = @DOOR, ROUTE = @ROUTE, SORTATION_LOCATION = @SORTATION_LOCATION, SORTATION_STATION = @SORTATION_STATION, BATCH_CARTON_ID = @BATCH_CARTON_ID, IS_CLOSED = @IS_CLOSED, QC_STATUS = @QC_STATUS, PD_UDF1 = @PD_UDF1, PD_UDF2 = @PD_UDF2, PD_UDF3 = @PD_UDF3, PICK_NOTES = @PICK_NOTES, RECEIPT_ID = @RECEIPT_ID, CROSS_DOCKED = @CROSS_DOCKED, SEQ_NO = @SEQ_NO, LABEL_TYPE = @LABEL_TYPE, COMPANY_PREFIX = @COMPANY_PREFIX, SERIAL_REFERENCE = @SERIAL_REFERENCE, OPTIMIZE_COP = @OPTIMIZE_COP, QTY_REJECTED = @QTY_REJECTED, REJECTED_REASON = @REJECTED_REASON, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID  ";
        private string SQL_UPDATE_PICK_DETAIL_STATUS = " UPDATE @_@PICK_DETAIL SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID ";
        private string SQL_UPDATE_PICK_DETAIL_STATUS_AND_TO_LOC = " UPDATE @_@PICK_DETAIL SET STATUS = @STATUS, TO_LOC = @TO_LOC, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID ";
        private string SQL_UPDATE_PICK_DETAIL_UPDATED_DATE_BY_PICK_DETAIL_ID = "UPDATE  @_@PICK_DETAIL SET UPDATED_BY = @UPDATED_BY, UPDATED_DATE = @UPDATED_DATE WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID ";
        private string STATUS = "@STATUS";
        private string STOP = "@STOP";
        private string TO_LOC = "@TO_LOC";
        private string TRACKING_ID = "@TRACKING_ID";
        private string TS = "@TS";
        private string UOM_ID = "@UOM_ID";
        private string UOM_QTY = "@UOM_QTY";
        private string UPDATE_SOURCE = "@UPDATE_SOURCE";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WAVE_ID = "@WAVE_ID";
        private string WH_ID = "@WH_ID";

        public int DeletePickDetailByID(string pickDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_DELETE_PICK_DETAIL.Replace("@_@", whLoginID), paras);
        }

        public int DeletePickDetailByID(DataBase dataBase, DbTransaction tran, string pickDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_PICK_DETAIL.Replace("@_@", whLoginID), paras);
        }

        public PickDetailInfo GetPickDetailByID(string pickdetailID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PICK_DETAIL_ID", pickdetailID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PickDetailInfo pickDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                }
            }
            return pickDetailInfo;
        }

        public PickDetailInfo GetPickDetailByID(DataBase dataBase, DbTransaction tran, string pickdetailID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PICK_DETAIL_ID = @PICK_DETAIL_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PICK_DETAIL_ID", pickdetailID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PickDetailInfo pickDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return pickDetailInfo;
        }

        public List<PickDetailInfo> GetPickDetailByLpnID(string LpnID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE LPN_ID = @LPN_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LPN_ID", LpnID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PickDetailInfo> pickDetailList = new List<PickDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    PickDetailInfo pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                    pickDetailList.Add(pickDetailInfo);
                }
            }
            return pickDetailList;
        }

        public List<PickDetailInfo> GetPickDetailByOrderID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PD.ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PickDetailInfo> pickDetailInfoList = new List<PickDetailInfo>();
            PickDetailInfo pickDetailInfo = null;
            try
            {
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
                {
                    while (reader.Read())
                    {
                        DateTime CS00002;
                        pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                        pickDetailInfoList.Add(pickDetailInfo);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return pickDetailInfoList;
        }

        public List<PickDetailInfo> GetPickDetailByOrderID(string orderID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PD.ORDER_ID = @ORDER_ID " + where;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PickDetailInfo> pickDetailInfoList = new List<PickDetailInfo>();
            PickDetailInfo pickDetailInfo = null;
            try
            {
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
                {
                    while (reader.Read())
                    {
                        DateTime CS00002;
                        pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                        pickDetailInfoList.Add(pickDetailInfo);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return pickDetailInfoList;
        }

        public List<PickDetailInfo> GetPickDetailByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PD.ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PickDetailInfo> pickDetailInfoList = new List<PickDetailInfo>();
            PickDetailInfo pickDetailInfo = null;
            try
            {
                IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
                while (reader.Read())
                {
                    DateTime CS00002;
                    pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                    pickDetailInfoList.Add(pickDetailInfo);
                }
                if (!reader.IsClosed)
                {
                    reader.Close();
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return pickDetailInfoList;
        }

        public List<PickDetailInfo> GetPickDetailByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PD.ORDER_ID = @ORDER_ID AND PD.ORDER_LINE_ID=@ORDER_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PickDetailInfo> pickDetailInfoList = new List<PickDetailInfo>();
            PickDetailInfo pickDetailInfo = null;
            try
            {
                IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
                while (reader.Read())
                {
                    DateTime CS00002;
                    pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                    pickDetailInfoList.Add(pickDetailInfo);
                }
                if (!reader.IsClosed)
                {
                    reader.Close();
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return pickDetailInfoList;
        }

        public DataSet GetPickDetailByQueryList(List<string> sqlWhere, PickDetailQueryEntity pickdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_PICK_DETAIL_WAVE_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!pickdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, pickdetailQuery.CurrentPage, pickdetailQuery.PageSize, pickdetailQuery.SortField, pickdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetPickDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, PickDetailQueryEntity pickdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!pickdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, pickdetailQuery.CurrentPage, pickdetailQuery.PageSize, pickdetailQuery.SortField, pickdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<string> GetPickDetailIDByLpn(string lpnID, string whLoginID)
        {
            string sql = this.SQL_SELECT_PICK_DETAIL_ID_BY_LPN.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LPN_ID", lpnID) };
            List<string> pickDetailIDs = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    pickDetailIDs.Add(reader["PICK_DETAIL_ID"].ToString());
                }
            }
            return pickDetailIDs;
        }

        public List<string> GetPickDetailIDByOrderID(string orderID, string status, string whLoginID)
        {
            string sql = this.SQL_SELECT_PICK_DETAIL_ID_BY_ORDER_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@STATUS", status) };
            List<string> pickDetailIDs = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    pickDetailIDs.Add(reader["PICK_DETAIL_ID"].ToString());
                }
            }
            return pickDetailIDs;
        }

        public List<string> GetPickDetailIDByOrderLineID(string orderID, string orderLineID, string status, string whLoginID)
        {
            string sql = this.SQL_SELECT_PICK_DETAIL_ID_BY_ORDER_LINE_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@STATUS", status) };
            List<string> pickDetailIDs = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    pickDetailIDs.Add(reader["PICK_DETAIL_ID"].ToString());
                }
            }
            return pickDetailIDs;
        }

        public List<PickDetailInfo> GetPickDetailsByID(List<string> pickDetailID, string whLoginID, PubParasInfo pubParasInfo)
        {
            List<PickDetailInfo> pickDetailInfos = new List<PickDetailInfo>();
            if ((pickDetailID != null) && (pickDetailID.Count > 0))
            {
                string sql = this.SQL_SELECT_ALL_PICK_DETAIL.Replace("@_@", whLoginID) + " WHERE PD.PICK_DETAIL_ID IN (";
                for (int i = 0; i < pickDetailID.Count; i++)
                {
                    sql = sql + StrUtils.FormatSQLStr(pickDetailID[i]) + ",";
                }
                sql = sql.Substring(0, sql.Length - 1) + ") ";
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
                PickDetailInfo pickDetailInfo = null;
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
                {
                    while (reader.Read())
                    {
                        DateTime CS00003;
                        pickDetailInfo = new PickDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["CASE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PREALLOCATE_PICK_DETAIL_ID"].ToString(), reader["LOT"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["UOM_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["UOM_QTY"].ToString() != "") ? decimal.Parse(reader["UOM_QTY"].ToString()) : 0M, (reader["MOVED_QTY"].ToString() != "") ? decimal.Parse(reader["MOVED_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["DROP_ID"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["CARTON_TYPE"].ToString(), reader["TO_LOC"].ToString(), reader["DO_REPLENISH"].ToString(), reader["REPLENISH_ZONE"].ToString(), reader["DO_CARTONIZE"].ToString(), reader["PICK_METHOD"].ToString(), reader["WAVE_ID"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["FROM_LOC"].ToString(), reader["TRACKING_ID"].ToString(), (reader["FREIGHT_CHARGES"].ToString() != "") ? ((double) float.Parse(reader["FREIGHT_CHARGES"].ToString())) : ((double) 0f), reader["INTER_MODAL_VEHICLE"].ToString(), reader["LOAD_ID"].ToString(), (reader["STOP"].ToString() != "") ? int.Parse(reader["STOP"].ToString()) : 0, reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["SORTATION_STATION"].ToString(), reader["BATCH_CARTON_ID"].ToString(), reader["IS_CLOSED"].ToString(), reader["QC_STATUS"].ToString(), reader["PD_UDF1"].ToString(), reader["PD_UDF2"].ToString(), reader["PD_UDF3"].ToString(), reader["PICK_NOTES"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CROSS_DOCKED"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["LABEL_TYPE"].ToString(), reader["COMPANY_PREFIX"].ToString(), reader["SERIAL_REFERENCE"].ToString(), reader["OPTIMIZE_COP"].ToString(), (reader["QTY_REJECTED"].ToString() != "") ? decimal.Parse(reader["QTY_REJECTED"].ToString()) : 0M, reader["REJECTED_REASON"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["SKU_DESCR"].ToString());
                        pickDetailInfos.Add(pickDetailInfo);
                    }
                }
            }
            return pickDetailInfos;
        }

        public int InsertPickDetail(PickDetailInfo pickdetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                pickdetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PickDetail_Parameters(pickdetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PICK_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertPickDetail(DataBase dataBase, DbTransaction tran, PickDetailInfo pickdetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                pickdetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PickDetail_Parameters(pickdetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PICK_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_PickDetail_Parameters(PickDetailInfo pickDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.PICK_DETAIL_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.MOVED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.DROP_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LOC, SqlDbType.VarChar, 20), 
                new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.UPDATE_SOURCE, SqlDbType.VarChar, 20), new SqlParameter(this.CARTON_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.CARTON_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.DO_REPLENISH, SqlDbType.VarChar, 1), new SqlParameter(this.REPLENISH_ZONE, SqlDbType.VarChar, 20), new SqlParameter(this.DO_CARTONIZE, SqlDbType.VarChar, 1), new SqlParameter(this.PICK_METHOD, SqlDbType.VarChar, 1), new SqlParameter(this.WAVE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EFFECTIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.FROM_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TRACKING_ID, SqlDbType.VarChar, 50), new SqlParameter(this.FREIGHT_CHARGES, SqlDbType.Float, 8), new SqlParameter(this.INTER_MODAL_VEHICLE, SqlDbType.VarChar, 30), 
                new SqlParameter(this.LOAD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STOP, SqlDbType.Int, 4), new SqlParameter(this.DOOR, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE, SqlDbType.VarChar, 20), new SqlParameter(this.SORTATION_LOCATION, SqlDbType.VarChar, 20), new SqlParameter(this.SORTATION_STATION, SqlDbType.VarChar, 20), new SqlParameter(this.BATCH_CARTON_ID, SqlDbType.VarChar, 20), new SqlParameter(this.IS_CLOSED, SqlDbType.VarChar, 1), new SqlParameter(this.QC_STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.PD_UDF1, SqlDbType.VarChar, 20), new SqlParameter(this.PD_UDF2, SqlDbType.VarChar, 20), new SqlParameter(this.PD_UDF3, SqlDbType.VarChar, 20), new SqlParameter(this.PICK_NOTES, SqlDbType.VarChar, 0xff), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CROSS_DOCKED, SqlDbType.VarChar, 1), new SqlParameter(this.SEQ_NO, SqlDbType.Int, 4), 
                new SqlParameter(this.LABEL_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.COMPANY_PREFIX, SqlDbType.VarChar, 20), new SqlParameter(this.SERIAL_REFERENCE, SqlDbType.VarChar, 20), new SqlParameter(this.OPTIMIZE_COP, SqlDbType.VarChar, 1), new SqlParameter(this.QTY_REJECTED, SqlDbType.Decimal, 13), new SqlParameter(this.REJECTED_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.PREALLOCATE_PICK_DETAIL_ID, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(pickDetailInfo.WhID))
            {
                paramArray[0].Value = pickDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = pickDetailInfo.PickDetailID;
            paramArray[2].Value = pickDetailInfo.CaseID;
            paramArray[3].Value = pickDetailInfo.OrderID;
            paramArray[4].Value = pickDetailInfo.OrderLineID;
            paramArray[5].Value = pickDetailInfo.Lot;
            paramArray[6].Value = pickDetailInfo.OwnerID;
            paramArray[7].Value = pickDetailInfo.SkuID;
            paramArray[8].Value = pickDetailInfo.AltSkuID;
            paramArray[9].Value = pickDetailInfo.UomID;
            paramArray[10].Value = pickDetailInfo.Qty;
            paramArray[11].Value = pickDetailInfo.UomQty;
            paramArray[12].Value = pickDetailInfo.MovedQty;
            paramArray[13].Value = pickDetailInfo.Status;
            paramArray[14].Value = pickDetailInfo.DropID;
            paramArray[15].Value = pickDetailInfo.Loc;
            paramArray[0x10].Value = pickDetailInfo.LpnID;
            paramArray[0x11].Value = pickDetailInfo.PackID;
            paramArray[0x12].Value = pickDetailInfo.UpdateSource;
            if (!string.IsNullOrEmpty(pickDetailInfo.CartonGroup))
            {
                paramArray[0x13].Value = pickDetailInfo.CartonGroup;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.CartonType))
            {
                paramArray[20].Value = pickDetailInfo.CartonType;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.ToLoc))
            {
                paramArray[0x15].Value = pickDetailInfo.ToLoc;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.DoReplenish))
            {
                paramArray[0x16].Value = pickDetailInfo.DoReplenish;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.ReplenishZone))
            {
                paramArray[0x17].Value = pickDetailInfo.ReplenishZone;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.DoCartonize))
            {
                paramArray[0x18].Value = pickDetailInfo.DoCartonize;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            paramArray[0x19].Value = pickDetailInfo.PickMethod;
            paramArray[0x1a].Value = pickDetailInfo.WaveID;
            paramArray[0x1b].Value = pickDetailInfo.EffectiveDate;
            if (!string.IsNullOrEmpty(pickDetailInfo.FromLoc))
            {
                paramArray[0x1c].Value = pickDetailInfo.FromLoc;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.TrackingID))
            {
                paramArray[0x1d].Value = pickDetailInfo.TrackingID;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = pickDetailInfo.FreightCharges;
            if (!string.IsNullOrEmpty(pickDetailInfo.InterModalVehicle))
            {
                paramArray[0x1f].Value = pickDetailInfo.InterModalVehicle;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.LoadID))
            {
                paramArray[0x20].Value = pickDetailInfo.LoadID;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            paramArray[0x21].Value = pickDetailInfo.Stop;
            if (!string.IsNullOrEmpty(pickDetailInfo.Door))
            {
                paramArray[0x22].Value = pickDetailInfo.Door;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.Route))
            {
                paramArray[0x23].Value = pickDetailInfo.Route;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.SortationLocation))
            {
                paramArray[0x24].Value = pickDetailInfo.SortationLocation;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.SortationStation))
            {
                paramArray[0x25].Value = pickDetailInfo.SortationStation;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.BatchCartonID))
            {
                paramArray[0x26].Value = pickDetailInfo.BatchCartonID;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            paramArray[0x27].Value = pickDetailInfo.IsClosed;
            paramArray[40].Value = pickDetailInfo.QcStatus;
            if (!string.IsNullOrEmpty(pickDetailInfo.PdUdf1))
            {
                paramArray[0x29].Value = pickDetailInfo.PdUdf1;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.PdUdf2))
            {
                paramArray[0x2a].Value = pickDetailInfo.PdUdf2;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.PdUdf3))
            {
                paramArray[0x2b].Value = pickDetailInfo.PdUdf3;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.PickNotes))
            {
                paramArray[0x2c].Value = pickDetailInfo.PickNotes;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.ReceiptID))
            {
                paramArray[0x2d].Value = pickDetailInfo.ReceiptID;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.CrossDocked))
            {
                paramArray[0x2e].Value = pickDetailInfo.CrossDocked;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            paramArray[0x2f].Value = pickDetailInfo.SeqNo;
            if (!string.IsNullOrEmpty(pickDetailInfo.LabelType))
            {
                paramArray[0x30].Value = pickDetailInfo.LabelType;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.CompanyPrefix))
            {
                paramArray[0x31].Value = pickDetailInfo.CompanyPrefix;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.SerialReference))
            {
                paramArray[50].Value = pickDetailInfo.SerialReference;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(pickDetailInfo.OptimizeCop))
            {
                paramArray[0x33].Value = pickDetailInfo.OptimizeCop;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            paramArray[0x34].Value = pickDetailInfo.QtyRejected;
            if (!string.IsNullOrEmpty(pickDetailInfo.RejectedReason))
            {
                paramArray[0x35].Value = pickDetailInfo.RejectedReason;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            paramArray[0x36].Value = pickDetailInfo.CreatedBy;
            paramArray[0x37].Value = pickDetailInfo.UpdatedBy;
            paramArray[0x38].Value = pickDetailInfo.PreallocatePickDetailID;
            return paramArray;
        }

        public int UpdatePickDetailStatus(string pickDetailID, string pickDetailStatus, string updatedBy, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", pickDetailStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PICK_DETAIL_STATUS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdatePickDetailStatus(DataBase dataBase, DbTransaction tran, string pickDetailID, string pickDetailStatus, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", pickDetailStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PICK_DETAIL_STATUS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePickDetailStatusAndPickToLoc(DataBase dataBase, DbTransaction tran, string pickDetailID, string pickDetailStatus, string pickToLoc, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", pickDetailStatus), new SqlParameter("@TO_LOC", pickToLoc), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PICK_DETAIL_STATUS_AND_TO_LOC.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePickDetailUpdateDateByPickDetailID(DataBase dataBase, DbTransaction tran, string pickDetailID, string UpdateDate, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UPDATED_DATE", UpdateDate), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PICK_DETAIL_UPDATED_DATE_BY_PICK_DETAIL_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

